﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace DataAccess
{
    /// <summary>
    /// 提供通用的访问单个数据库的一系列方法
    /// </summary>
    public class Database
    {
        protected DbProviderFactory dbProviderFactory { get; set; }
        protected string connectionString { get; set; }

        /// <summary>
        /// 该数据库的在程序集中的唯一标识
        /// </summary>
        public string Name { get; private set; }

        internal Database()
        {

        }

        /// <summary>
        /// 根据提供程序和链接字符串初始化 <see cref="DataAccess.Database"/> 实例
        /// </summary>
        /// <param name="name">用户区别多数据库时好记的名称</param>
        /// <param name="providerName">提供程序程序集名称 <see cref="System.Data.SqlClient"/>、<see cref="System.Data.OleDb"/>、<see cref="System.Data.Odbc"/>、<see cref="System.Data.OracleClient"/></param>
        /// <param name="connectionString">连接字符串</param>
        public Database(string name, string providerName, string connectionString)
        {
            Name = name;
            dbProviderFactory = DbProviderFactories.GetFactory(providerName);
            connectionString = connectionString;
        }

        protected virtual bool SetTransaction(DbCommand cmd)
        {
            return false;
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        protected virtual DbConnection CreateConnection()
        {
            DbConnection conn = dbProviderFactory.CreateConnection();
            conn.ConnectionString = connectionString;
            return conn;
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <returns></returns>
        public virtual Transaction BeginTransaction()
        {
            return new Transaction(dbProviderFactory, connectionString);
        }

        /// <summary>
        /// 以指定的隔离级别启动数据库事务
        /// </summary>
        /// <param name="isolationLevel">指定事务的隔离级别</param>
        /// <returns></returns>
        public virtual Transaction BeginTransaction(IsolationLevel isolationLevel)
        {
            return new Transaction(dbProviderFactory, connectionString, isolationLevel);
        }
        /// <summary>
        /// 把键值对数组参数转换为 <see cref="System.Data.Common.DbParameter"/> 数组
        /// </summary>
        /// <param name="parameters">查询参数数组</param>
        /// <returns></returns>
        public DbParameter[] ConvertParameter(Parameter[] parameters)
        {

            if (parameters == null || parameters.Length == 0)
            {
                return null;
            }
            DbParameter[] dbParameters = new DbParameter[parameters.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                dbParameters[i] = CreateParameter(parameters[i].Name, parameters[i].Value);
            }
            return dbParameters;
        }

        /// <summary>
        /// 根据适配器创建查询参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        /// <param name="paramDirection">参数类型</param>
        /// <returns></returns>
        public DbParameter CreateParameter(string name, Object value = null, ParameterDirection paramDirection = ParameterDirection.Input)
        {
            DbParameter p = dbProviderFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value ?? DBNull.Value;
            p.Direction = paramDirection;
            return p;
        }

        public DbParameter CreateParameter(string name)
        {
            return CreateParameter(name, null, ParameterDirection.Input);
        }

        /// <summary>
        /// 创建一系列输入参数
        /// </summary>
        /// <param name="paramNames">参数名称，以逗号(,)分割</param>
        /// <param name="paramValues">参数值</param>
        /// <returns></returns>
        public DbParameter[] CreateParameters(string paramNames, params object[] paramValues)
        {
            if (paramNames == null)
            {
                throw new ArgumentNullException("paramNames");
            }
            string[] names = paramNames.Split(new char[] { ',' });
            int idx = 0;
            DbParameter[] parameters = new DbParameter[names.Length];
            foreach (string name in names)
            {
                string paraName = name.Trim();
                object paraValue = (paramValues == null) ? null : paramValues[idx];
                parameters[idx] = CreateParameter(paraName, paraValue);
                idx++;
            }
            return parameters;
        }

        /// <summary>
        /// 对DbConnection执行指定操作
        /// </summary>
        /// <param name="action">要对DbConnection执行操作的委托</param>
        public void ExecuteConnection(Action<DbConnection> action)
        {
            using (DbConnection conn = CreateConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                if (action != null)
                {
                    action(conn);
                }
            }
        }

        /// <summary>
        /// 执行查询，并返回受影响的行数
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string cmdText, params Parameter[] parameters)
        {
            return ExecuteNonQuery(cmdText, ConvertParameter(parameters));
        }

        /// <summary>
        /// 执行查询，并返回受影响的行数
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/></param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string cmdText, DbParameter[] parameters, CommandType cmdType = CommandType.Text)
        {
            DbConnection conn = CreateConnection();
            int ret = 0;
            bool isTransaction = false;
            using (DbCommand cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                isTransaction = SetTransaction(cmd);
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                ret = cmd.ExecuteNonQuery();
            }
            if (!isTransaction)
            {
                conn.Dispose();
            }
            return ret;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        /// <returns>结果集中的第一行第一列</returns>
        public Object ExecuteScalar(string cmdText, params Parameter[] parameters)
        {
            return ExecuteScalar(cmdText, ConvertParameter(parameters), CommandType.Text);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/></param>
        /// <returns>结果集中的第一行第一列</returns>
        public Object ExecuteScalar(string cmdText, DbParameter[] parameters, CommandType cmdType = CommandType.Text)
        {
            DbConnection conn = CreateConnection();
            object ret = null;
            bool isTransaction = false;
            using (DbCommand cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                isTransaction = SetTransaction(cmd);
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                ret = cmd.ExecuteScalar();
            }
            if (!isTransaction)
            {
                conn.Dispose();
            }
            return ret;
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.Common.DbDataReader"/>，当<see cref="System.Data.Common.DbDataReader"/>关闭时，系统会自动关闭关联的<see cref="System.Data.Common.DbConnection"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string cmdText, params Parameter[] parameters)
        {
            return ExecuteReader(cmdText, ConvertParameter(parameters), CommandType.Text);
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.Common.DbDataReader"/>，当<see cref="System.Data.Common.DbDataReader"/>关闭时，系统会自动关闭关联的<see cref="System.Data.Common.DbConnection"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/></param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string cmdText, DbParameter[] parameters, CommandType cmdType = CommandType.Text)
        {
            DbConnection conn = CreateConnection();

            using (DbCommand cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                bool isTransaction = SetTransaction(cmd);
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                var reader = cmd.ExecuteReader(isTransaction ? CommandBehavior.Default : CommandBehavior.CloseConnection);
                return reader;
            }
        }

        /// <summary>
        /// 查询数据库并对<see cref="System.Data.Common.DbDataReader"/>执行指定委托
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="dataReader">用于读取<see cref="System.Data.Common.DbDataReader"/>的方法</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        public void ExecuteReader(string cmdText, Action<DbDataReader> dataReader, params Parameter[] parameters)
        {
            ExecuteReader(cmdText, dataReader, ConvertParameter(parameters), CommandType.Text);
        }

        /// <summary>
        /// 查询数据库并对DbDataReader执行指定委托
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="dataReader">用于读取DbDataReader的方法</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/> </param>
        public void ExecuteReader(string cmdText, Action<DbDataReader> dataReader, DbParameter[] parameters, CommandType cmdType = CommandType.Text)
        {
            DbConnection conn = CreateConnection();
            bool isTransaction = false;
            using (DbCommand cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                isTransaction = SetTransaction(cmd);
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                var reader = cmd.ExecuteReader();
                if (dataReader != null)
                {
                    dataReader(reader);
                }
                cmd.Cancel();
                reader.Close();
            }
            if (!isTransaction)
            {
                conn.Dispose();
            }
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.DataTable"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string cmdText, params Parameter[] parameters)
        {
            return ExecuteDataTable(cmdText, ConvertParameter(parameters), CommandType.Text);
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.DataTable"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/></param>
        /// <param name="getSchema">是否获取架构信息</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string cmdText, DbParameter[] parameters, CommandType cmdType = CommandType.Text, bool getSchema = false)
        {
            DataTable dt = new DataTable();
            DbConnection conn = CreateConnection();
            bool isTransaction = false;
            using (DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    isTransaction = SetTransaction(cmd);
                    cmd.CommandText = cmdText;
                    cmd.CommandType = cmdType;
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    adapter.SelectCommand = cmd;
                    if (getSchema)
                    {
                        adapter.FillSchema(dt, SchemaType.Source);
                    }
                    else
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            if (!isTransaction)
            {
                conn.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.DataSet"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数，Parameter数组</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string cmdText, params Parameter[] parameters)
        {
            return ExecuteDataSet(cmdText, ConvertParameter(parameters), CommandType.Text);
        }

        /// <summary>
        /// 查询数据库并返回 <see cref="System.Data.DataSet"/>
        /// </summary>
        /// <param name="cmdText">查询语句或存储过程的命令</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="cmdType">命令类型<see cref="System.Data.CommandType"/></param>
        /// <param name="getSchema">是否获取架构信息</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string cmdText, DbParameter[] parameters, CommandType cmdType = CommandType.Text, bool getSchema = false)
        {
            DataSet ds = new DataSet();
            DbConnection conn = CreateConnection();
            bool isTransaction = false;
            using (DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    isTransaction = SetTransaction(cmd);
                    cmd.CommandType = cmdType;
                    cmd.CommandText = cmdText;
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    adapter.SelectCommand = cmd;
                    if (getSchema)
                    {
                        adapter.FillSchema(ds, SchemaType.Source);
                    }
                    else
                    {
                        adapter.Fill(ds);
                    }
                }
            }
            if (!isTransaction)
            {
                conn.Dispose();
            }
            return ds;
        }
    }
}
